package com.eolcum.evo.persistence;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataBaseHelper extends SQLiteOpenHelper {

	@SuppressLint("SdCardPath")
	private static String DB_PATH;
	

	private static String DB_NAME = "test.db";

	private SQLiteDatabase myDataBase;

	private final Context myContext;

	private Boolean hasSystemUpdate = false;
	private List<Integer> updatedTimePrograms = new ArrayList<Integer>();

	public DataBaseHelper(Context context) {
		super(context, DB_NAME, null, 1);
		this.myContext = context;
	}

	@Override
	public synchronized void close() {

		if (myDataBase != null)
			myDataBase.close();

		super.close();

	}

	@Override
	public void onCreate(SQLiteDatabase db) {

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	public void createDataBase() throws IOException {

		boolean dbExist = checkDataBase();

		if (dbExist) {
			// do nothing - database already exist
		} else {

			// By calling this method and empty database will be created into
			// the default system path
			// of your application so we are gonna be able to overwrite that
			// database with our database.
			this.getReadableDatabase();

			try {

				copyDataBase();

			} catch (IOException e) {

				throw new Error("Error copying database");

			}
		}

	}

	/**
	 * Check if the database already exist to avoid re-copying the file each
	 * time you open the application.
	 * 
	 * @return true if it exists, false if it doesn't
	 */
	private synchronized boolean checkDataBase() {

		SQLiteDatabase checkDB = null;

		try {
		    if(android.os.Build.VERSION.SDK_INT >= 4.2){
		        DB_PATH = myContext.getApplicationInfo().dataDir + "/databases/";         
		    } else {
		       DB_PATH = "/data/data/" + myContext.getPackageName() + "/databases/";
		    }
			String myPath = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READONLY);

		} catch (SQLiteException e) {

			// database does't exist yet.

		}

		if (checkDB != null) {

			checkDB.close();

		}

		return checkDB != null ? true : false;
	}

	/**
	 * Copies your database from your local assets-folder to the just created
	 * empty database in the system folder, from where it can be accessed and
	 * handled. This is done by transfering bytestream.
	 * */
	private synchronized void copyDataBase() throws IOException {

		// Open your local db as the input stream
		InputStream myInput = myContext.getAssets().open(DB_NAME);

		// Path to the just created empty db
		String outFileName = DB_PATH + DB_NAME;

		// Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);

		// transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}

		// Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();

	}

	public synchronized void openDataBase() throws SQLException {

		// Open the database
		String myPath = DB_PATH + DB_NAME;
		myDataBase = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READONLY);

	}

	public synchronized Apartment getApartment() {
		final String query = "SELECT * FROM APARTMENT;";
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);
		Apartment apt = null;
		if (cursor.moveToFirst()) {
			do {
				apt = new Apartment();
				apt.setId(Integer.parseInt(cursor.getString(0)));
				apt.setName(cursor.getString(1));
				apt.setRooms(getRooms(apt.getId()));
			} while (cursor.moveToNext());
		}

		Log.d("getAllBooks()", apt.toString());

		// return books
		return apt;
	}

	public synchronized List<Room> getRooms(Integer apartmentId) {
		String query;
		if (apartmentId == null)
			query = "SELECT * FROM ROOM;";
		else
			query = "SELECT * FROM ROOM WHERE APARTMENT_ID = "
					+ apartmentId.intValue() + ";";
		List<Room> rooms = new ArrayList<Room>();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			do {
				Room room = new Room();
				room.setId(Integer.parseInt(cursor.getString(0)));
				room.setName(cursor.getString(1));
				rooms.add(room);
			} while (cursor.moveToNext());
		}
		for (int i = 0; i < rooms.size(); i++) {
			rooms.get(i).setActuators(getActuators(rooms.get(i).getId()));
			rooms.get(i).setSensors(getSensors(rooms.get(i).getId()));
		}
		return rooms;
	}

	public synchronized List<Actuator> getActuators(Integer roomId) {
		String query;
		if (roomId == null)
			query = "SELECT * FROM ACTUATOR;";
		else
			query = "SELECT * FROM ACTUATOR WHERE ROOM_ID = "
					+ roomId.intValue() + ";";
		List<Actuator> actuators = new ArrayList<Actuator>();

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			do {
				Actuator actuator = new Actuator();
				actuator.setId(Integer.parseInt(cursor.getString(0)));
				actuator.setSerialNo(cursor.getString(1));
				actuator.setRoomId(Integer.parseInt(cursor.getString(2)));
				actuators.add(actuator);
			} while (cursor.moveToNext());
		}

		return actuators;
	}

	public  synchronized List<Sensor> getSensors(Integer roomId) {
		String query;
		if (roomId == null)
			query = "SELECT * FROM SENSOR;";
		else
			query = "SELECT * FROM SENSOR WHERE ROOM_ID = " + roomId.intValue()
					+ ";";
		List<Sensor> sensors = new ArrayList<Sensor>();

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			do {
				Sensor sensor = new Sensor();
				sensor.setHasTemp("1".equals(cursor.getString(2)));
				sensor.setHasOccup("1".equals(cursor.getString(1)));
				sensor.setHasHumid("1".equals(cursor.getString(0)));
				sensor.setId(Integer.parseInt(cursor.getString(3)));
				sensor.setSerialNo(cursor.getString(4));
				sensor.setRoomId(Integer.parseInt(cursor.getString(5)));
				sensors.add(sensor);
			} while (cursor.moveToNext());
		}
		return sensors;
	}

	public synchronized Double getInstantSetpoint(Integer roomId) {
		if (roomId == null)
			return null;
		List<ScheduleSetpoint> weeklySetpoints = new ArrayList<ScheduleSetpoint>();
		String query = "SELECT * FROM S_WEEKLY_SCHEDULE WHERE ROOM_ID = "
				+ roomId + ";";
		try {
			Calendar cal = Calendar.getInstance();
			Calendar now = Calendar.getInstance();

			SQLiteDatabase db = this.getWritableDatabase();
			Cursor cursor = db.rawQuery(query, null);

			if (cursor.moveToFirst()) {
				do {

					cal = Calendar.getInstance();
					ScheduleSetpoint scSp = new ScheduleSetpoint();
					scSp.setTempSetpoint(Double.parseDouble(cursor.getString(4)));
					cal.set(Calendar.DAY_OF_WEEK,
							Integer.parseInt(cursor.getString(1)));
					cal.set(Calendar.HOUR_OF_DAY,
							Integer.parseInt(cursor.getString(2)));
					cal.set(Calendar.MINUTE,
							Integer.parseInt(cursor.getString(3)));
					if (cal.before(now))
						cal.add(Calendar.DAY_OF_YEAR, 7);
					scSp.setTime(new Date(cal.getTimeInMillis()));
					weeklySetpoints.add(scSp);
				} while (cursor.moveToNext());
			}

			long bestMatch = 0;
			int bestMatchIndex = -1;
			for (int i = 0; i < weeklySetpoints.size(); i++) {
				if (weeklySetpoints.get(i).getTime().getTime()
						- now.getTimeInMillis() > bestMatch) {
					bestMatch = weeklySetpoints.get(i).getTime().getTime()
							- now.getTimeInMillis();
					bestMatchIndex = i;
				}
			}
			if (weeklySetpoints.size() == 0) {
				return (double) 21;
			} else
				return weeklySetpoints.get(bestMatchIndex).getTempSetpoint();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	public synchronized List<ScheduleSetpoint> getDailySchedule(Integer roomId) {
		if (roomId == null)
			return null;
		List<ScheduleSetpoint> weeklySetpoints = new ArrayList<ScheduleSetpoint>();
		String query = "SELECT * FROM S_WEEKLY_SCHEDULE WHERE ROOM_ID = "
				+ roomId + ";";
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(query, null);

		Calendar cal = Calendar.getInstance();
		Calendar now = Calendar.getInstance();
		if (cursor.moveToFirst()) {

			do {
				cal = Calendar.getInstance();
				ScheduleSetpoint scSp = new ScheduleSetpoint();
				scSp.setTempSetpoint(Double.parseDouble(cursor.getString(4)));
				cal.set(Calendar.DAY_OF_WEEK,
						Integer.parseInt(cursor.getString(1)));
				cal.set(Calendar.HOUR_OF_DAY,
						Integer.parseInt(cursor.getString(2)));
				cal.set(Calendar.MINUTE,
						Integer.parseInt(cursor.getString(3)));
				if (cal.before(now))
					cal.add(Calendar.DAY_OF_YEAR, 7);
				scSp.setTime(new Date(cal.getTimeInMillis()));
				weeklySetpoints.add(scSp);
			} while (cursor.moveToNext());
		}
		now.add(Calendar.DAY_OF_YEAR, 1);
		long oneDayLater = now.getTimeInMillis();

		List<ScheduleSetpoint> setpoints = new ArrayList<ScheduleSetpoint>();
		for (int i = 0; i < weeklySetpoints.size(); i++) {
			if (weeklySetpoints.get(i).getTime().getTime() < oneDayLater) {
				setpoints.add(weeklySetpoints.get(i));
			}
		}
		return setpoints;

	}

	public synchronized Boolean getHasSystemUpdate() {
		return hasSystemUpdate;
	}

	public synchronized void setHasSystemUpdate(Boolean hasSystemUpdate) {
		this.hasSystemUpdate = hasSystemUpdate;
	}

	public synchronized List<Integer> getUpdatedTimePrograms() {
		return updatedTimePrograms;
	}

	public synchronized void setUpdatedTimePrograms(List<Integer> updatedTimePrograms) {
		this.updatedTimePrograms = updatedTimePrograms;
	}
	public synchronized void setLogOccupancy(String sensorId) {
		if(sensorId == null || "".equals(sensorId))
			return;
		SQLiteDatabase db = this.getWritableDatabase();
 
        ContentValues values = new ContentValues();
        values.put("MOMENT", Calendar.getInstance().getTimeInMillis());
        values.put("SENSOR_ID", sensorId);
 
        db.insert("L_OCCUP", null, values); 
        db.close(); 
	}
	
	public synchronized void setLogRTC(String roomId, String setpoint, String activeInput, String activeOutput) {
		if (roomId == null)
			return;
		SQLiteDatabase db = this.getWritableDatabase();
		 
        ContentValues values = new ContentValues();
        values.put("MOMENT", Calendar.getInstance().getTimeInMillis());
        values.put("ROOM_ID", roomId);
        values.put("SETPOINT", setpoint);
        values.put("INPUT", activeInput);
        values.put("OUTPUT", activeOutput);
 
        db.insert("L_RTC", null, values); 
        db.close(); 
	}
	public synchronized List<RTCLog> getLogRTCList(int roomId) {
		SQLiteDatabase db = this.getWritableDatabase();
        final String query = "SELECT * FROM L_RTC WHERE ROOM_ID=" + roomId + ";";
        List<RTCLog> resultList = new ArrayList<RTCLog>();
        Cursor cursor = db.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			do {
				RTCLog log = new RTCLog();
				log.setTimeStamp(Long.parseLong(cursor.getString(0)));
				log.setActuatorOutput(Double.parseDouble(cursor.getString(4)));
				log.setSetpoint(Double.parseDouble(cursor.getString(2)));
				log.setRoomTemperature(Double.parseDouble(cursor.getString(3)));
				log.setRoomId(roomId);
				resultList.add(log);
			} while (cursor.moveToNext());
		}
		return resultList;
	}
	public synchronized long insertSensor(Sensor sensor) {
		if (sensor.getId() < 0 )
			return -1;
		long id = -1;
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put("SENSOR_ID", "" + sensor.getId());
		values.put("SERIAL_NO", "" + sensor.getSerialNo());
		values.put("OCCUPIED", (sensor.isActiveOccupied() ? "1" : "0"));
		values.put("HUMIDITY", "" + sensor.getActiveHumidity());
		values.put("TEMPERATURE", "" + sensor.getActiveTemp());
		values.put("ROOM_ID", "" + sensor.getRoomId());
		values.put("TIME_IN_MILLIS", "" + sensor.getLastActiveTimeInMillis());
		values.put("HAS_TEMP", (sensor.isHasTemp() ? "1" : "0"));
		values.put("HAS_OCCUP", (sensor.isHasOccup() ? "1" : "0"));
		values.put("HAS_HUMID", (sensor.isHasHumid() ? "1" : "0"));
		
		id = db.insert("L_SENSOR", null, values);
		db.close();
		return id;
	}

	public synchronized long insertRTCLog(RTCLog rtcLog) {

		if (rtcLog.getRoomId() < 0 )
			return -1;
		long id = -1;
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put("MOMENT", "" + rtcLog.getTimeStamp());
		values.put("ROOM_ID", "" + rtcLog.getRoomId());
		values.put("SETPOINT", "" + rtcLog.getSetpoint());
		values.put("INPUT", "" + rtcLog.getRoomTemperature());
		values.put("OUTPUT", "" + rtcLog.getActuatorOutput());

		id = db.insert("L_RTC", null, values);
		db.close();
		return id;
	}

	public synchronized List<Sensor> getSensorHistory(String id, String serialNo, long minTimeStamp,
			long maxTimeStamp) {
		List<Sensor> sensorHistory = new ArrayList<Sensor>();
		
		String[] tableColumns = new String[] {
			    "SERIAL_NO", "SENSOR_ID", "ROOM_ID", "TIME_IN_MILLIS", "TEMPERATURE",
			    "HUMIDITY", "OCCUPIED", "HAS_TEMP", "HAS_OCCUP", "HAS_HUMID"
			};
		
		String whereClause = "";
		if(id != null)
			whereClause += "ROOM_ID = ?";
		if(whereClause.length() > 0) whereClause += " AND ";
		if(serialNo != null)
			whereClause += "SERIAL_NO = ?";
		if(whereClause.length() > 0) whereClause += " AND ";
		whereClause += "TIME_IN_MILLIS >= ? AND TIME_IN_MILLIS <= ?";
		
		String[] whereArgs = new String[] {id, serialNo, Long.toString(minTimeStamp), Long.toString(maxTimeStamp)};
		String orderBy = "TIME_IN_MILLIS";
		
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor c = db.query("table1", tableColumns, whereClause, whereArgs,
		        null, null, orderBy);
		
//		if(c.moveToFirst()) {
//			do {
//				Sensor sensor = new Sensor();
//				sensor.setActiveHumidity(c.getColumnIndex(columnName)
//			}
//		}
		return sensorHistory;
	}
}